package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pits.dto.AdminCourseListDTO;
import pits.util.Constants;
import pits.util.Logger;

public class AdminCourseListDAO {

	public List<AdminCourseListDTO> getData() {
		List<AdminCourseListDTO> result = new ArrayList<AdminCourseListDTO>();
		AdminCourseListDTO runnerDTO = null;

		Connection conn = null;
		String query = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;

		// ResultSet courseexpResultSet = null;
		ResultSet courseResultSet = null;
		ResultSet departResultSet = null;
		ResultSet langResultSet = null;
		ResultSet DBResultSet = null;
		ResultSet frameResultSet = null;
		ResultSet techResultSet = null;

		try {
			conn = pits.util.Connection.getConnection();

			query = "SELECT * FROM course WHERE isDeleted = 0";
			sqlStatement = conn.prepareStatement(query);
			courseResultSet = sqlStatement.executeQuery();

			while (courseResultSet.next()) {

				runnerDTO = new AdminCourseListDTO();

				query = "SELECT * FROM department WHERE DepartID=" + courseResultSet.getInt("DepartID")
				        + " AND isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				departResultSet = sqlStatement.executeQuery();
				departResultSet.next();

				query = "SELECT * FROM language l, course_language cl" + " WHERE cl.CourseID="
				        + courseResultSet.getInt("CourseID") + " AND l.LangID=cl.LangID" + " AND l.isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				langResultSet = sqlStatement.executeQuery();
				String languages = "-";
				while (langResultSet.next()) {
					languages += ", " + langResultSet.getString("Name");
				}
				if (!languages.equals("-")) {
					languages = languages.substring(2);
				}

				query = "SELECT Name FROM pits_db.database d, course_database cd" + " WHERE cd.CourseID="
				        + courseResultSet.getInt("CourseID") + " AND d.DatabaseID=cd.DatabaseID" + " AND d.isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				DBResultSet = sqlStatement.executeQuery();
				String DBMS = "-";
				while (DBResultSet.next()) {
					DBMS += ", " + DBResultSet.getString("Name");
				}
				if (!DBMS.equals("-")) {
					DBMS = DBMS.substring(2);
				}

				query = "SELECT * FROM framework f, course_framework cf" + " WHERE cf.CourseID="
				        + courseResultSet.getInt("CourseID") + " AND f.FrameID=cf.FrameID" + " AND f.isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				frameResultSet = sqlStatement.executeQuery();
				String frameTech = "-";
				while (frameResultSet.next()) {
					frameTech += ", " + frameResultSet.getString("Name");
				}

				query = "SELECT * FROM technology t, course_technology ct" + " WHERE ct.CourseID="
				        + courseResultSet.getInt("CourseID") + " AND t.TechID=ct.TechID" + " AND t.isDeleted=0";
				// new Logger().write(query);
				sqlStatement = conn.prepareStatement(query);
				techResultSet = sqlStatement.executeQuery();
				while (techResultSet.next()) {
					frameTech += ", " + techResultSet.getString("Name");
				}
				if (!frameTech.equals("-")) {
					frameTech = frameTech.substring(2);
				}

				runnerDTO.setName(courseResultSet.getString("Name"));
				runnerDTO.setDepartName(departResultSet.getString("DepartName"));
				runnerDTO.setPeriod(courseResultSet.getString("Period"));
				runnerDTO.setDescription(courseResultSet.getString("Description"));
				runnerDTO.setLanguage(languages);
				runnerDTO.setFrameTech(frameTech);
				runnerDTO.setDBMS(DBMS);
				runnerDTO.setDateIssused(courseResultSet.getString("DateIssused"));
				runnerDTO.setCourseID(courseResultSet.getInt("CourseID"));

				result.add(runnerDTO);
			}
		} catch (SQLException sqlEx) {
			new Logger().writeln(sqlEx.toString());
		} finally {
			try {
				// courseResultSet.close();
				// departResultSet.close();
				// langResultSet.close();
				// DBResultSet.close();
				// frameResultSet.close();
				// techResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new Logger().writeln(sqlEx.toString());
			}
		}
		// new Logger().write("TakenCourseBriefDAO.getData: Done");
		return result;
	}
}
